package dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.util.ArrayList;
import java.util.List;

import javax.swing.JOptionPane;

import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;

/**
 * This class holds the methods for *all* the queries in the gtbay project.
 * 
 * @author Lee
 * @version 1.0
 */
public class ExecuteQuery 
{
	public static String url="jdbc:mysql://localhost:3306/gtbaysample";
	public static String username="root";
	public static String password="cs4400";
	private static ArrayList<String> sVals;
	private static ResultSet set;
	private static Double getNowDouble;
	private static String maxAmount = "";
	
	/**
	 * Generic method for opening a connection to mysql
	 * 
	 * @param username
	 * @param password
	 * @return
	 */
	public static Connection OpenConnection(String username, String password)
	{
		Connection con=null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(url, username, password);
		} catch (Exception e)
		{
			e.printStackTrace();
		}
		return con;
	}
	
	/**
	 * Generic method for closing a connection to mysql
	 * 
	 * @param con
	 */
	public static void CloseConnection(Connection con)
	{
		try {
			con.close();
		} catch (Exception e)
		{
			e.printStackTrace();
		}
	}
	
	/**
	 * Login query
	 * 
	 * @param parUser
	 * @param parPass
	 * @return
	 */
	public static boolean login(String parUser, String parPass) 
	{
		Connection con = OpenConnection(username, password);

		boolean retVal = false; 
		try {
			String query = "SELECT password FROM user WHERE username = '" + parUser + "'";
			Statement statement = con.createStatement();
			ResultSet rs = statement.executeQuery(query);
			while (rs.next())
			{
				if (rs.getString(1).equals(parPass))
					retVal = true;
			}
			con.close();
		} catch(SQLException e) {
			e.printStackTrace();
		}
		return retVal;
	}
	
	/**
	 * Generic method that handles insert queries.
	 * 
	 * @param query
	 * @return
	 */
	public static boolean insertQuery(String query)
	{
		boolean retVal = false;
		Connection con = OpenConnection(username, password);
		try {
			Statement statement = con.createStatement();
			statement.executeUpdate(query);
			retVal = true;
		} catch(MySQLIntegrityConstraintViolationException e) {
			JOptionPane.showMessageDialog(null, "Duplication in database.");
		}
		catch (SQLException e) {
			JOptionPane.showMessageDialog(null, "Error in reading to the database");
			e.printStackTrace();
		}
		return retVal;
	}
	
	/**
	 * This method handles rating items
	 * 
	 * @param itemID
	 * @param user
	 * @param star
	 * @param comment
	 */
	public static void rateItem(String itemID, String user, String star, String comment)
	{
		Date theDate = new Date(System.currentTimeMillis());
		Time time = new Time(System.currentTimeMillis());
		String date = theDate + " " + time;
		String query = "INSERT INTO `RATES` (`Username`, `ItemID`, `Stars`, `Comment`, `DateAndTime`) " + 
		"VALUES ('" + user + "', '" + itemID + "', '" + star + "', '" + 
		comment + "', '" + date + "');";
		boolean work = insertQuery(query);
		if (work) JOptionPane.showMessageDialog(null, "Item rated!");
	}
	
	/**
	 * This method handles the "get it now" function in gtbay
	 * 
	 * @param itemID
	 * @param username
	 * @param price
	 */
	public static void getNow(int itemID, String username, double price)
	{
		Date theDate = new Date(System.currentTimeMillis());
		Time time = new Time(System.currentTimeMillis());
		String date = theDate + " " + time;
		String query = "INSERT INTO `BID` (`Username_BID`, `ItemID`, `DateAndTime`, "
			+ "Amount) VALUE( '" + username + "', '" + itemID + "', '" + date + "', " +
			price + ");";
		insertQuery(query);
		String query2= "UPDATE ITEM SET AuctionEndDate='" + date + "' " +
		"WHERE ItemID=" + itemID + ";";
		insertQuery(query2);
	}
	
	/**
	 * This is a generic method that handles select queries.
	 * 
	 * @param query
	 * @return
	 */
	public static ResultSet execute(String query)
	{
		Connection con = OpenConnection(username, password);
		ResultSetMetaData mdata = null;
		try {
			Statement statement = con.createStatement();
			set = statement.executeQuery(query);
		} catch(SQLException e) { e.printStackTrace(); }
		return set;
	}
	
	/**
	 * IsAdmin checks to see if a user is an administrator (used with getposition to 
	 * list the position the home page if an admin logs in).
	 * 
	 * @param user
	 * @return
	 */
	public static boolean isAdmin(String user) {
		String query = "SELECT username FROM ADMINUSER";
		boolean retVal = false;
		try {
			ResultSet set = execute(query);
			ResultSetMetaData mdata = set.getMetaData();
			while (set.next()) {
				for (int i = 1; i <= mdata.getColumnCount(); i++) {
					String dummy = set.getString(i);
					if (dummy.equals(user)) retVal = true;
				}
			}
		} catch(SQLException e) { e.printStackTrace(); }
		return retVal;
	}
	
	/**
	 * Gets the position for admin users.
	 * 
	 * @param user
	 * @return
	 */
	public static String getPosition(String user) {
		String query = "SELECT position FROM ADMINUSER WHERE username='" + user + "';";
		String dummy = null;
		try {
			ResultSet set = execute(query);
			ResultSetMetaData mdata = set.getMetaData();
			while (set.next()) {
				for (int i = 1; i <= mdata.getColumnCount(); i++)
				{
					dummy = set.getString(i);
				}
			}
		}catch (SQLException e) {}
		return dummy;
	}
	
	/**
	 * Deletes ratings from the database.
	 * 
	 * @param username
	 * @param itemID
	 */
	public static void deleteRating(String username, String itemID)
	{
		String query = "" + 
		"DELETE FROM RATES WHERE Username = '" + username + "' AND itemID=" + itemID + ";";
		insertQuery(query);
	}
	
	/**
	 * Returns a list of all ratings attached to an item in the database.
	 * 
	 * @param itemID
	 * @return
	 */
	public static List<String> viewRatings(int itemID)
	{
		List<String> retVal = new ArrayList<String>();
		String query1 = "Select avg(stars) from rates where itemid=" + itemID + " group by itemid";
		try {
			ResultSet set = execute(query1);
			ResultSetMetaData mdata = set.getMetaData();
			while (set.next())
			{
				for (int i = 1; i <= mdata.getColumnCount(); i++)
					retVal.add(set.getString(i));
			}
		} catch(SQLException e) {
			e.printStackTrace();
		}

		String query = "" +
		"SELECT Rates.ItemID, IName, RATES.Username, Stars, DateAndTime, Comment " +
		"From RATES INNER JOIN ITEM ON RATES.ItemID=Item.ItemID " + 
		"WHERE RATES.ItemID = " + itemID + " " +
		"ORDER BY DateAndTime DESC";
		try {
			ResultSet set = execute(query);
			ResultSetMetaData mdata = set.getMetaData();
			while (set.next())
			{
				for (int i = 1; i <= mdata.getColumnCount(); i++)
					retVal.add(set.getString(i));
			}
		} catch(SQLException e) {
			e.printStackTrace();
		}
		
		return retVal;
	}
	
	/**
	 * EditDescription is a method that allows users (who have listed an item) 
	 * to edit its description in the database.
	 * 
	 * @param description
	 * @param itemID
	 */
	public static void editDescription(String description, int itemID)
	{
		String query = "UPDATE ITEM " +
		"SET Description='" + description + "' " + 
		"WHERE ItemID=" + itemID;
		insertQuery(query);
	}
	
	/**
	 * This method handles placing a bid in the databsse.
	 * 
	 * @param username
	 * @param bid
	 * @param itemID
	 */
	public static void placeABid(String username, String bid, String itemID)
	{
		Date theDate = new Date(System.currentTimeMillis());
		Time time = new Time(System.currentTimeMillis());
		Double theBid = Double.parseDouble(bid);
		String date = theDate + " " + time;
		
		String query0 = "select auctionenddate from item where itemid=" + itemID + " AND auctionenddate >= '" + date + "';";
		System.out.println(query0);
		ResultSet set = execute(query0);
		String endDate = "";
		try {
			ResultSetMetaData mdata = set.getMetaData();
			while (set.next())
			{
				for (int i = 1; i <= mdata.getColumnCount(); i++)
				{
					endDate += set.getString(i);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		if (query0.equals(""))
		{
			JOptionPane.showMessageDialog(null, "Actually, the auction ended right as you were bidding, so the bid was not placed!");
			return;
		}
		
		String query = "" + 
		"INSERT INTO `BID` (`Username_BID`, `ItemID`, `DateAndTime`, `Amount`) " + 
		"VALUES ( '" + username + "', '" + itemID + "', '" + date + "', '" + 
		theBid + "');";
		insertQuery(query);
	}
	
	/**
	 * This method handles listing a new item in the database.
	 * 
	 * @param itemName
	 * @param description
	 * @param category
	 * @param condition
	 * @param startPrice
	 * @param minPrice
	 * @param auctionEnd
	 * @param getNow
	 * @param returns
	 * @param uname
	 * @param time
	 */
	public static void postItem(String itemName, String description, String category, 
			int condition, double startPrice, double minPrice, Date auctionEnd, 
			Double getNow, boolean returns, String uname, Time time)
	{
		String dummy = System.currentTimeMillis() + "";
		String timestamp = auctionEnd + " " + time;
		int itemId = Integer.parseInt(dummy.substring(4, dummy.length()));
		String query = "INSERT INTO `ITEM` (`ItemID`, `Item_condition`, `StartingBid`," +
		"`MinSalePrice`, `AuctionEndDate`, `Returns`, `GetItNowPrice`, `Description`," +
		"`Iname`, `Cname`, `User_name`)" +
		" VALUES (" + itemId + ", " + condition + ", " + startPrice + ", " + 
		minPrice + ", '" + timestamp + "', " + returns + ", " + getNow + ", '" + 
		description + "', '" + itemName + "', '" + category + "', '" + uname + "');";
		insertQuery(query);
	}
	
	/**
	 * This method handles viewing an item after a user clicks on its 
	 * respective itemid in the search results panel.
	 * 
	 * @param itemID
	 * @return
	 */
	public static List<String> viewItem(int itemID)
	{
		String query = "SELECT  Iname, Description, Cname, Item_condition, Returns, GetItNowPrice, AuctionEndDate, User_name, StartingBid " + 
		"FROM ITEM " + 
		"WHERE ItemID=" + itemID;
		String query2 = "" +
		"SELECT Amount, DateAndTime, Username_BID " +
		"FROM bid " +
		"WHERE ItemID=" + itemID + " " +  
		"ORDER BY Amount DESC;";
		List<String> resultList = new ArrayList<String>();
		try {
			ResultSet set = execute(query);
			ResultSetMetaData mdata = set.getMetaData();
			while (set.next())
			{
				for (int i = 1; i <= mdata.getColumnCount(); i++)
					resultList.add(set.getString(i));
			}
			ResultSet set2 = execute(query2);
			ResultSetMetaData mdata2 = set2.getMetaData();
			while (set2.next())
			{
				for (int i = 1; i <= mdata2.getColumnCount(); i++) {
					resultList.add(set2.getString(i));
				}
			}
		} catch(SQLException e) { e.printStackTrace();}
		return resultList;
	}

	
	/**
	 * This handles entering new users into the database (non admin, of course).
	 * 
	 * @param fName
	 * @param lName
	 * @param uName
	 * @param pass
	 */
	public static void register(String fName, String lName, String uName, String pass)
	{
		String query = "INSERT INTO `USER` (`Firstname`, `Lastname`, `Username`, `Password`)" + 
		" VALUES ('" + fName + "', '" + lName + "', '" + uName + "', '" + pass + "');";
		insertQuery(query);
	}
	
	/**
	 * This method handles viewing the category report.
	 * 
	 * @return
	 */
	public static ResultSet viewCatReport()
	{
		String query = "" +
		"SELECT Cname, Count(*), " + 
		"Min(GetItNowPrice), " +
		"Max(GetItNowPrice), " +
		"Avg(GetItNowPrice) " +
		"FROM ITEM " +
	//	"WHERE nullif(GetItNowPrice, '') " +
		"GROUP BY CName " +
		"ORDER BY CName ";

		return execute(query);
	}
	
	/**
	 * This method returns a set of all auctions that have ended with 
	 * their respective winners.
	 * 
	 * @return
	 */
	public static ResultSet viewAuctionResults()
	{
		Date date = new Date(System.currentTimeMillis());
		Time time = new Time(System.currentTimeMillis());
		String dateTime = date + " " + time;

		String query = "" +
		"SELECT TEMP2.ItemID, IName, Amount, Username_BID, AuctionEndDate " +
		"FROM ITEM AS I JOIN (SELECT TEMP.ItemID, TempBid.Amount, Username_BID " +
		"FROM BID AS TempBid JOIN " +
		"(SELECT ItemID, max(Amount) AS MAXI " +
		"FROM BID " +
		"GROUP BY ItemID) AS TEMP ON TempBid.ItemID = TEMP.ItemID " +
		"WHERE TempBid.Amount = TEMP.MAXI) AS TEMP2 ON I.ItemID = TEMP2.ItemID " + 
		"WHERE i.AuctionEndDate < '" + dateTime + "' UNION " + 
		"select item.itemid, iname, null, null, auctionenddate " + 
		"from item where auctionenddate < '" + dateTime + "' AND " + 
		"not exists(select * from bid where bid.itemid=item.itemid) " + 
		"order by auctionenddate desc;";

		return execute(query);
	}
	
	public static void clearAuctionResults()
	{
		String query0 = "delete from auction_results;";
		insertQuery(query0);
		
	}
	
	public static void insertIntoAuctionResults(String itemid, String price, String winner, String date)
	{
		String query = "INSERT INTO `gtbaysample`.`auction_results` "  + 
		"(`ITEMID`, `SalePrice`, `Username`, `AUCTIONENDED`) VALUES " + 
		"(" + itemid + "," + price + ",'" + winner + "', '" + date + "') ";
		insertQuery(query);
		
	}

	/**
	 * This method handles viewing the user report (sold, purchased, listed, rated).
	 * @return
	 */
	public static ResultSet viewUserReport()
	{
		Date date = new Date(System.currentTimeMillis());
		Time time = new Time(System.currentTimeMillis());
		String dateTime = date + " " + time; 
		
		String query0 = "CREATE OR REPLACE VIEW USER1 AS " +
		"SELECT User_name as username, count(*) AS listed " +
		"From ITEM " +
		"GROUP BY User_name; ";
		insertQuery(query0);
		
//		String querySTAR = "CREATE OR REPLACE VIEW maxbidders as " + 
//		"select max(amount) as theMax from bid group by itemid";
//		insertQuery(querySTAR);

		String query1 = "CREATE OR REPLACE VIEW USER2 AS " + 
		"SELECT user.username, count(*) as Sold " +
		"FROM USER INNER JOIN ITEM on USER.username = ITEM.User_name INNER JOIN Auction_results on ITEM.ItemID = Auction_results.ItemID " +
		"WHERE Auction_results.Username IS NOT NULL " +
		"GROUP BY user.username;";
		insertQuery(query1);

		String query2 = "CREATE OR REPLACE VIEW USER3 AS " + 
		"SELECT Username, count(*) as Purchased " +
		"FROM Auction_results " + 
		"WHERE Username IS NOT NULL " +
		"GROUP BY Username; ";
		insertQuery(query2);

		String query3 = "CREATE OR REPLACE VIEW USER4 AS " +
		"SELECT Username, count(*) as Rated " +
		"FROM RATES " +
		"GROUP BY Username; ";
		insertQuery(query3);

		String query4 = "CREATE OR REPLACE VIEW USER5 AS " + 
		"SELECT user1.username as username, sold,listed " + 
		"FROM USER1 LEFT OUTER JOIN USER2 ON USER1.username = USER2.Username;";
		insertQuery(query4);

		String query5 = "CREATE OR REPLACE VIEW USER6 AS " +
		"SELECT User3.username AS username, purchased, rated " + 
		"FROM USER3 LEFT OUTER JOIN USER4 ON USER3.username = USER4.username " +
		"UNION " +
		"SELECT User4.username AS username, purchased, rated " +
		"FROM USER3 RIGHT OUTER JOIN USER4 ON USER3.username = USER4.username;";
		insertQuery(query5);

		String query6= "CREATE OR REPLACE VIEW USER_REPORT1 AS " +
		"SELECT USER6.username as username, listed, sold, purchased, rated " +
		"FROM USER6 LEFT OUTER JOIN USER5 ON USER6.username = USER5.username " +
		"UNION " +  
		"SELECT USER5.username as username, listed, sold, purchased, rated " +
		"FROM USER6 RIGHT OUTER JOIN USER5 ON USER6.username = USER5.username;";
		insertQuery(query6);

		String query7 = "CREATE OR REPLACE VIEW USER_REPORT AS " + 
		"SELECT username, IFNULL(listed, 0) AS listed, IFNULL(sold, 0) as sold, IFNULL(purchased, 0) as purchased, IFNULL(rated, 0) as rated " +
		"FROM USER_REPORT1;";
		insertQuery(query7);

		String query8 = "Select * from user_report";

		return execute(query8);
	}
	
	/**
	 * This method handles searching for items with multiple parameters.
	 * 
	 * @param keyword
	 * @param category
	 * @param minimumPrice
	 * @param maximumPrice
	 * @param condition
	 * @return
	 */
	public static ResultSet searchItem(String keyword, String category, String minimumPrice,
			String maximumPrice, String condition)
	{
		Date theDate = new Date(System.currentTimeMillis());
		Time time = new Time(System.currentTimeMillis());
		String date = theDate + " " + time;

		String query1 = "select max(amount) from item left outer join bid on item.itemid=bid.itemid";
		boolean test = true;
		String theMax = null;
		try {
			Connection con = OpenConnection(username, password);
			Statement statement = con.createStatement();
			ResultSet set = statement.executeQuery(query1);
			ResultSetMetaData mdata = set.getMetaData();
			while (set.next())
			{
				for (int i = 1; i < mdata.getColumnCount(); i++)
				{
					theMax = set.getString(i);
				}
			}

		} catch(Exception e) {} 
		if (theMax == null) test = false;
		
		String query = "" + 
		"SELECT item.ItemID, Iname, ";
		if (!test)
			query += "currentBid, ";
		else 
			query += "null, ";
		query += "User_name, GetItNowPrice, AuctionEndDate " +
		"FROM item LEFT OUTER JOIN (Select itemid, max(amount) as currentBid from bid group by itemid) as temp ON item.ItemID=temp.ItemID " +
		"WHERE item.AuctionEndDate > '" + date + "' AND "; 
		if (test)
			query += "currentBid >= " + minimumPrice + " AND currentBid <=" + maximumPrice + " ";
		else
			query+= "StartingBid >= " + minimumPrice + " AND StartingBid <=" + maximumPrice + " ";
//		"ifnull(temp.currentBid, startingbid) >= " + minimumPrice + " AND ifnull(temp.currentBid,startingbid) <= " + maximumPrice + " ";
		if (!category.equals(""))
			query += "AND cname='" + category + "' ";	
		query += "AND (item.iname LIKE '" + keyword + "' " + 
		"OR item.description LIKE '" + keyword + "') ";
		if (!condition.equals(""))
			query += "AND item.item_condition >= " + condition + " ";
		query += "Group by item.ItemID " +
		"Order by AuctionEndDate DESC;";

		System.out.println(query);
		return execute(query);
	}
}
